﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QLDBHYT.VO;
using System.Data;
using System.Data.SqlClient;


namespace QLDBHYT.DAO
{
    class SttDAO
    {
        private dbConnection conn;
        public SttDAO()
        {
            conn = new dbConnection();
        }
        public Decimal GetStt_Ct_id2(int nam,int soluong)
        {
            dbConnection conn;
            Decimal ct_id0 = 0;
            conn = new dbConnection();
            conn.openConnection();
            conn.transaction = conn.conn.BeginTransaction();
            string query = string.Format("SELECT Dmstt.* FROM dbo.dmstt Dmstt where nam = @nam");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[0].Value = nam;
            DataTable tb = conn.executeSelectQuery(query, sqlParameters,conn.transaction);
            foreach (DataRow dr in tb.Rows)
            {
                ct_id0 = Convert.ToDecimal(dr["ct_id0"]) + 1;

            }
            query = string.Format("UPDATE dbo.dmstt SET ct_id0=@ct_id0 "
            + " WHERE nam=@nam");
            sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[0].Value = nam;
            sqlParameters[1] = new SqlParameter("@ct_id0", SqlDbType.Decimal);
            sqlParameters[1].Value = ct_id0+soluong;
            conn.executeUpdateQuery(query, sqlParameters,conn.transaction);
            conn.transaction.Commit();
            return ct_id0;
        }
        public Decimal GetStt_Ct_id2(int nam)
        {
            dbConnection conn;
            Decimal ct_id0=0;
            conn = new dbConnection();
            string query = string.Format("SELECT Dmstt.* FROM dbo.dmstt Dmstt where nam = @nam");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[0].Value = nam;
            DataTable tb = conn.executeSelectQuery(query, sqlParameters);
            foreach (DataRow dr in tb.Rows)
            {
                ct_id0 = Convert.ToDecimal(dr["ct_id0"]) + 1;
                
            }
            query = string.Format("UPDATE dbo.dmstt SET ct_id0=@ct_id0 "
            + " WHERE nam=@nam");
            sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[0].Value = nam;
            sqlParameters[1] = new SqlParameter("@ct_id0", SqlDbType.Decimal);
            sqlParameters[1].Value = ct_id0;
            conn.executeUpdateQuery(query, sqlParameters);

            return ct_id0;
        }
        public Decimal GetStt_Ct_id2_Noi(int nam,int soluong)
        {
            dbConnection conn;
            Decimal ct_id0 = 0;
            conn = new dbConnection();
            conn.openConnection();
            conn.transaction = conn.conn.BeginTransaction();
            string query = string.Format("SELECT Dmstt.* FROM dbo.dmstt Dmstt where nam = @nam");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[0].Value = nam;
            DataTable tb = conn.executeSelectQuery(query, sqlParameters, conn.transaction);
            foreach (DataRow dr in tb.Rows)
            {
                ct_id0 = Convert.ToDecimal(dr["stt_rec0"]) + 1;

            }
            query = string.Format("UPDATE dbo.dmstt SET stt_rec0=@ct_id0 "
            + " WHERE nam=@nam");
            sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[0].Value = nam;
            sqlParameters[1] = new SqlParameter("@ct_id0", SqlDbType.Decimal);
            sqlParameters[1].Value = ct_id0+soluong;
            conn.executeUpdateQuery(query, sqlParameters, conn.transaction);
            conn.transaction.Commit();
            return ct_id0;
        }
        public Decimal GetStt_Ct_id2_Noi(int nam)
        {
            dbConnection conn;
            Decimal ct_id0 = 0;
            conn = new dbConnection();
            string query = string.Format("SELECT Dmstt.* FROM dbo.dmstt Dmstt where nam = @nam");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[0].Value = nam;
            DataTable tb = conn.executeSelectQuery(query, sqlParameters);
            foreach (DataRow dr in tb.Rows)
            {
                ct_id0 = Convert.ToDecimal(dr["stt_rec0"]) + 1;

            }
            query = string.Format("UPDATE dbo.dmstt SET stt_rec0=@ct_id0 "
            + " WHERE nam=@nam");
            sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[0].Value = nam;
            sqlParameters[1] = new SqlParameter("@ct_id0", SqlDbType.Decimal);
            sqlParameters[1].Value = ct_id0;
            conn.executeUpdateQuery(query, sqlParameters);

            return ct_id0;
        }
        public SttVO GetStt(int nam)
        {
            dbConnection conn;
            SttVO sttVO = new SttVO();
            conn = new dbConnection();
            string query = string.Format("SELECT Dmstt.* FROM dbo.dmstt Dmstt where nam = @nam");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[0].Value = nam;
            DataTable tb = conn.executeSelectQuery(query, sqlParameters);
            foreach (DataRow dr in tb.Rows)
            {
                sttVO.Ctid = Convert.ToDecimal(dr["ct_id"]) + 1;
                sttVO.Ctid0 = Convert.ToDecimal(dr["ct_id0"]) + 1;
                sttVO.Ctid2 = Convert.ToDecimal(dr["ct_id2"]) + 1;
                sttVO.Phieung = Convert.ToDecimal(dr["phieu_ng"]) + 1;
                sttVO.Nam = nam;
            }
            return sttVO;
        }
        public SttVO GetSttNoi(int nam)
        {
            dbConnection conn;
            SttVO sttVO = new SttVO();
            conn = new dbConnection();
            string query = string.Format("SELECT Dmstt.* FROM dbo.dmstt Dmstt where nam = @nam");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[0].Value = nam;
            DataTable tb = conn.executeSelectQuery(query, sqlParameters);
            foreach (DataRow dr in tb.Rows)
            {
                sttVO.Ctid = Convert.ToDecimal(dr["stt_rec"]) + 1;
                sttVO.Ctid0 = Convert.ToDecimal(dr["stt_rec0"]) + 1;
                sttVO.Ctid2 = Convert.ToDecimal(dr["stt_rec2"]) + 1;
                sttVO.Phieung = Convert.ToDecimal(dr["phieu_no"]) + 1;
                sttVO.Nam = nam;
            }
            return sttVO;
        }
        public bool UpdateStt(int nam, SttVO sttVO)
        {
            dbConnection conn;
            conn = new dbConnection();
            string query = string.Format("UPDATE dbo.dmstt SET ct_id=@P1,ct_id0=@P2,ct_id2 =@P3,phieu_ng = @P4 "
            + " WHERE nam=@P5");

            SqlParameter[] sqlParameters = new SqlParameter[5];

            sqlParameters[0] = new SqlParameter("@p1", SqlDbType.Decimal);
            sqlParameters[0].Value = sttVO.Ctid;

            sqlParameters[1] = new SqlParameter("@p2", SqlDbType.Decimal);
            sqlParameters[1].Value = sttVO.Ctid0-1;

            sqlParameters[2] = new SqlParameter("@p3", SqlDbType.Decimal);
            sqlParameters[2].Value = sttVO.Ctid2 + 11;

            sqlParameters[3] = new SqlParameter("@p4", SqlDbType.Decimal);
            sqlParameters[3].Value = sttVO.Phieung;

            sqlParameters[4] = new SqlParameter("@p5", SqlDbType.Int);
            sqlParameters[4].Value = sttVO.Nam;
            return conn.executeUpdateQuery(query, sqlParameters);
        }
        public bool UpdateSttNoi(int nam, SttVO sttVO)
        {
            dbConnection conn;
            conn = new dbConnection();
            string query = string.Format("UPDATE dbo.dmstt SET stt_rec=@P1,stt_rec0=@P2,stt_rec2 =@P3,phieu_no = @P4 "
            + " WHERE nam=@P5");

            SqlParameter[] sqlParameters = new SqlParameter[5];

            sqlParameters[0] = new SqlParameter("@p1", SqlDbType.Decimal);
            sqlParameters[0].Value = sttVO.Ctid;

            sqlParameters[1] = new SqlParameter("@p2", SqlDbType.Decimal);
            sqlParameters[1].Value = sttVO.Ctid0 - 1;

            sqlParameters[2] = new SqlParameter("@p3", SqlDbType.Decimal);
            sqlParameters[2].Value = sttVO.Ctid2 + 13;

            sqlParameters[3] = new SqlParameter("@p4", SqlDbType.Decimal);
            sqlParameters[3].Value = sttVO.Phieung;

            sqlParameters[4] = new SqlParameter("@p5", SqlDbType.Int);
            sqlParameters[4].Value = sttVO.Nam;
            return conn.executeUpdateQuery(query, sqlParameters);
        }
    }
}
